package cn.com.checne.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import cn.com.checne.model.domain.LanguageInfo;
import cn.com.checne.model.domain.NavigateInfo;


@Repository
public class NavigateInfoDao {
	protected static final Logger log = Logger.getLogger(NavigateInfoDao.class);

	/**
	 * create
	 * @param na
	 * @return
	 */
	public boolean create(NavigateInfo na) {
		boolean result = false;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String createSql = "insert into tbl_navigate(na_name,na_desc,na_mark,na_order,lg_id_fk) values(?,?,?,?,?)";
			PreparedStatement pstmt = conn.prepareStatement(createSql);
			int i = 0 ;
			pstmt.setString(++i,na.getName());
			pstmt.setString(++i,na.getDesc());
			pstmt.setString(++i,na.getMark());
			pstmt.setInt(++i,na.getOrder());
			pstmt.setInt(++i,na.getLanguageId());
			pstmt.execute();
			conn.close();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false ;
		}
		return result;
	}

	/**
	 * @param na
	 * @return
	 */
	public boolean update(NavigateInfo na) {
		boolean result = false;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String updateSql = "update tbl_navigate set na_name = ?,na_desc = ? ,na_mark = ? ,lg_id_fk = ? ,na_order = ? where id = ? ";
			PreparedStatement pstmt = conn.prepareStatement(updateSql);
			int i = 0 ;
			pstmt.setString(++i,na.getName());
			pstmt.setString(++i,na.getDesc());
			pstmt.setString(++i,na.getMark());
			pstmt.setInt(++i,na.getLanguageId());
			pstmt.setInt(++i,na.getOrder());
			pstmt.setInt(++i,na.getId());
			pstmt.executeUpdate();
			conn.close();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false ;
		}
		return result;
	}

	/**
	 * @param id
	 * @return
	 */
	public boolean delete(int id){
		boolean result = false;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String updateSql = "delete from tbl_navigate where id = ? ";
			PreparedStatement pstmt = conn.prepareStatement(updateSql);
			int i = 0 ;
			pstmt.setInt(++i,id);
			pstmt.executeUpdate();
			conn.close();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false ;
		}
		return result ;
	}
	public NavigateInfo getNavigate(int naId) {
		NavigateInfo na = new NavigateInfo();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_navigate where id = ? ";

			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, naId);
			ResultSet rs = pstmt.executeQuery();
			List<NavigateInfo> list = getResult(rs);
			na = (list != null && list.size() > 0 ) ? list.get(0) : na ;
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return na;
	}

	public NavigateInfo getNavigate(String mark,int languageId) {
		NavigateInfo navi = new NavigateInfo();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_navigate where na_mark = ? and lg_id_fk = ? ";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, mark);
			pstmt.setInt(2, languageId);
			ResultSet rs = pstmt.executeQuery();
			List<NavigateInfo> list = getResult(rs);
			navi = (list != null && list.size() > 0 ) ? list.get(0) : navi ;
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return navi;
	}
	
	public List<NavigateInfo> getAll(int languageId) {
		List<NavigateInfo> list = new ArrayList<NavigateInfo>();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_navigate ";
			if(languageId > 0){
				sql += " where lg_id_fk = ?" ;
			}
			sql += " order by na_order asc" ;
			PreparedStatement pstmt = conn.prepareStatement(sql);
			if(languageId > 0){
				pstmt.setInt(1,languageId);
			}
			ResultSet rs = pstmt.executeQuery();
			list.addAll(getResult(rs));
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * get rseult from resultSet
	 * @param rs
	 * @return
	 */
	public List<NavigateInfo> getResult(ResultSet rs) {
		List<NavigateInfo> list = new ArrayList<NavigateInfo>();
		try {
			NavigateInfo na = null ;
			while(rs.next()) {
				na = new NavigateInfo();
				na.setId(rs.getInt("id"));
				na.setName(rs.getString("na_name"));
				na.setDesc(rs.getString("na_desc"));
				na.setMark(rs.getString("na_mark"));
				na.setOrder(rs.getInt("na_order"));
				na.setLanguageId(rs.getInt("lg_id_fk"));
				LanguageInfo lg = new LanguageInfoDao().getLanguageInfoById(na.getLanguageId());
				na.setLanguageInfo(lg);
				
				list.add(na);
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
}